'''
Created on Oct 25, 2009

@author: paul
'''
#! /usr/bin/python
import MySQLdb,sys,os,string
from xml.sax.handler import ContentHandler
from xml.sax import parse
import logging
import os

_logger = logging.getLogger("mx.org.came.Schema.updateApp")

class schemaReader():
    global myHandler

    def parseFile(self,path):
        self.myHandler=AppHandler()
        with open("{0}".format(path),'r') as f:
            parse(f,self.myHandler)
            f.close()

    def getAppDict(self):
        return self.myHandler.appDict
    
class AppHandler(ContentHandler):
    #These are CLASS VARIABLE By default is PUBLIC
    global appDict,schemasDict,fieldsDict,curSID,curFID,curSchemaName
    
    def __init__(self):
        self.appDict={}
        self.schemasDict={}
        self.fieldsDict={}
        self.appDict['schemas']=self.schemasDict
    
    def startElement(self,name,attrs): 
        if name=='application': 
            nameList=attrs.getNames()
            for i in range(len(nameList)):
                self.appDict[nameList[i]]=attrs.getValue(nameList[i])
        '''
        appDict is a Dictionary which holds the attributes of application
        '''
        if name=='schema':
            self.curSID=attrs.getValue('id')
            self.curSchemaName=attrs.getValue('name')
        
        if name=='field':
            field={}
            field['name']=attrs.getValue('name')
            #field['type']=attrs.getValue('type')
            field['range']=attrs.getValue('range')
            self.fieldsDict[attrs.getValue('id')]=field
            self.curFID=attrs.getValue('id')
            '''
            field id will be the key of the fieldsDict.
            name and range of the field are the value of the key
            '''
        
        if name=='choice':
            '''
            Each choice will runs into this function
            Thus, the field may have choices or 
            this is the first choice.
            '''
            field=self.fieldsDict[self.curFID]
            
            if 'choice' in field:
                chList=field['choice']
                chList.append(attrs.getValue('name'))
                self.fieldsDict[self.curFID]=field
                '''
                Choice is a LIST in the field dictionary
                '''
            else:
                chList=[attrs.getValue('name')]
                field['choice']=chList
                self.fieldsDict[self.curFID]=field
    
    def endElement(self,name):
        '''
        Here is the end of a schema.
        '''
        if name=='schema': 
            self.fieldsDict['name']=self.curSchemaName
            self.schemasDict[self.curSID]=self.fieldsDict.copy()
            self.fieldsDict.clear()

def createDB(name):
    sql='''
        CREATE DATABASE `{0}`
        '''.format(name)
    #print sql
    try:
        conn=MySQLdb.connect(host="localhost",
                             user="root",
                             passwd="password")
        cursor=conn.cursor()
        cursor.execute(sql)
        cursor.close()
        conn.commit()
        conn.close()
        return True
    except MySQLdb.Error ,e:
        ret="Error %d: %s" % (e.args[0],e.args[1])
        _logger.exception(ret)
        return False

def createAppInfoDB():
    sql='''
        CREATE DATABASE `AppInfo`
        '''
    #print sql
    try:
        conn=MySQLdb.connect(host="localhost",
                             user="root",
                             passwd="password")
        cursor=conn.cursor()
        cursor.execute(sql)
        cursor.close()
        conn.commit()
        conn.close()
        return True
    except MySQLdb.Error ,e:
        ret="Error %d: %s" % (e.args[0],e.args[1])
        _logger.exception(ret)
        return False
    
def createAppInfoTable():
    sql='''
        CREATE TABLE `AppInfo`.`Info` (
                `id` INT NOT NULL ,
                `path` TEXT NOT NULL ,
                PRIMARY KEY ( `id` )
        ) ENGINE = MYISAM ;
        '''
    execSQL(sql,'AppInfo')
    
def InsertAppInfo(id,path):
    path= path.replace('\\', '\\\\') #'''{0}{1}{2}'''.format(os.path.dirname(path),'\\\\',os.path.basename(path))
    _logger.debug(path)
    sql='''INSERT INTO `AppInfo`.`Info` 
                (`id` ,`path`) VALUES ('{0}','{1}') ON DUPLICATE KEY UPDATE `path`='{2}' '''.format(id,path,path)
    _logger.debug(sql)
    execSQL(sql,'AppInfo')
    
def createTable(dbName,schemaDict):
    sName=schemaDict.pop('name')
    sql='''
        CREATE TABLE `{0}`.`{1}` (
        '''.format(dbName,sName)
    for i in range(len(schemaDict)):
        # String type
        if schemaDict[str(i)]['range']=='0':
            sql="{0}{1}".format(sql,"`{0}` TEXT NULL,".format(schemaDict[str(i)]['name']))
        else:
            sql="{0}{1}".format(sql,"`{0}` INT NULL,".format(schemaDict[str(i)]['name']))
        #if i != len(schemaDict)-1:
            #sql="{0}{1}".format(sql,',')
    sql='''{0} `Time` TIMESTAMP NOT NULL ) ENGINE = MYISAM ;'''.format(sql)
    #sql="{0}{1}".format(sql,") ENGINE = MYISAM ;")
    _logger.debug(sql)
    execSQL(sql,dbName)

def execSQL(SQL,dbName):
    try:
        conn=MySQLdb.connect(host="localhost",
                             user="root",
                             passwd="password",
                             db=dbName)
        cursor=conn.cursor()
        cursor.execute(SQL)
        cursor.close()
        conn.commit()
        conn.close()
        return True
    except MySQLdb.Error ,e:
        ret="Error %d: %s" % (e.args[0],e.args[1])
        return ret

def main():
    path=os.path.abspath(sys.argv[1])
    _logger.debug(path)
    asc=schemaReader()
    asc.parseFile(path)
    createDB(asc.getAppDict()[u'name'])
    createAppInfoDB()
    createAppInfoTable()
    InsertAppInfo(asc.getAppDict()[u'id'],path)
    for val in asc.getAppDict()[u'schemas'].itervalues():
        createTable(asc.getAppDict()[u'name'],val)
        #print val
        #print "================"
    
if __name__ == '__main__':
    main()
